<?php
/* Jose Trujillo
 * - Define queries for SQL
 * - Define insert/updaye for SQL
 * - Defines useful functions for SQL
 */

//// Format
//// X_Y_Z
//// X: Query (Q), Update (U), Insert (I), Delete (D)
//// Y: Represents the result of Query
//// Z: Represents the parameters for Where

/* ***** *
 * * ** General Queries - Not Being Used ** *
// Query Log In
$Q_UserName_UserPass =
   "SELECT M.MemberId, M.FirstName, M.Paid
    FROM Membership AS M
    WHERE M.MemberId='%s' AND M.Password=MD5('%s')";

// Query Paid
$Q_Paid_User =
   "SELECT M.Paid
    FROM Membership AS M
    WHERE M.MemberId='%s'";

// Update Change Password
$U_None_UserPass =
   "UPDATE Membership AS M
    SET M.Password=MD5('%s')
    WHERE M.MemberId='%s' AND M.Password=MD5('%s')
    LIMIT 1";

// Get User EMail from UserId
$Q_User_Email =
   "SELECT M.MemberId
    FROM Membership AS M
    WHERE M.Email='%s'";

// Update Forgot Password
$U_None_User =
   "UPDATE Membership AS M
    SET M.Password=MD5('%s')
    WHERE M.MemberId='%s'
    LIMIT 1";

 * ** General Queries - Not Being Used ** *
 * ***** */

//// Verified After Change UserId for Email (See J)

// Get User EMail - Forgot Password - J
$Q_User_Email_ResetPassw =
   "SELECT *
    FROM Membership AS M
    WHERE M.Email='%s' AND (M.Status='A' OR M.Status='a')";

// Update Password - Forgot Password - J
$U_None_Email_ResetPassw =
   "UPDATE Membership AS M
    SET M.Password=MD5('%s')
    WHERE M.Email='%s' AND (M.Status='A' OR M.Status='a')
    LIMIT 1";

// Query Log In - J
$Q_EmailNamePaid_EmailPassw_Login =
   "SELECT M.Email, M.FirstName, M.Paid
    FROM Membership AS M
    WHERE M.Email='%s' AND M.Password=MD5('%s') AND (M.Status='A' OR M.Status='a')";

// Query Upcoming Blocked Events - J
$Q_ALL_NONE_Events_StatusUorB =
   "SELECT *
    FROM Events AS E
    WHERE E.Status='U' OR E.Status='u' OR E.Status='B' OR E.Status='b'";

// Query Past Events - J
$Q_ALL_NONE_EventsPast_Date =
   "SELECT *
    FROM Events AS E
    WHERE (E.Status='N' OR E.Status='n')  AND E.Date < '%s'
    ORDER BY E.Date DESC
    LIMIT " . MAX_EVENTS_PAST_DISPLAY;

// Query Future Events - J
$Q_ALL_NONE_EventsFuture_Date =
   "SELECT *
    FROM Events AS E
    WHERE (E.Status='N' OR E.Status='n')  AND E.Date > '%s' 
    ORDER BY E.Date ASC
    LIMIT " . MAX_EVENTS_FUTURE_DISPLAY;


// Query Upcoming Blocked Events & Member Attendance - J
$Q_ALL_Eventid_EventMemberAttend =
   "SELECT *
    FROM EventMemberAttend AS EMA
    WHERE EMA.EventId=%d AND EMA.EmailId='%s'
    LIMIT 1";

// Insert Event Member Attend
$I_ALL_EventMemberAttend =
    "INSERT INTO EventMemberAttend
     (EventId, EmailId, Attendance, Guests) 
     VALUES (%d, '%s', '%s', %d)";

// Update Event Member Attend
$U_ALL_EventMemberAttend =
    "UPDATE EventMemberAttend 
    SET Attendance='%s', Guests=%d
    WHERE EventId=%d AND EmailId='%s'
    LIMIT 1";

// Update Change Password - J
$U_None_Newpassw_EmailOldpass =
   "UPDATE Membership AS M
    SET M.Password=MD5('%s')
    WHERE M.Email='%s' AND M.Password=MD5('%s')
    LIMIT 1";

//// DB Table Event - J
$Q_EventidTitleSpeakerDate_StartdateEnddateOrdering =
    "SELECT  E.EventId, E.Title, E.SpeakerName, E.Date
    FROM Events AS E
    WHERE E.Date > '%s' AND E.Date < '%s' AND E.Status <> 'C'
    ORDER BY %s";


// Query Detail Event - J
$Q_ALL_Eventid_EventDetail =
   "SELECT *
    FROM Events AS E
    WHERE E.EventId=%s
    LIMIT 1";

// Query Cookie Authorizer - J
$Q_NamePaid_Email_AuthorizeCookie =
   "SELECT M.FirstName, M.Paid
    FROM Membership AS M
    WHERE M.Email='%s' AND (M.Status='A' OR M.Status='a')";

?>